package dbhelper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * SQLite帮助类，建表及更新表，打开数据库
 * 
 * @author king
 * 
 */
public class DBOpenHelper extends SQLiteOpenHelper {

    public DBOpenHelper(Context context) {
	super(context, "water.db", null, 2);
    }

    /**
     * 建表
     */
    @Override
    public void onCreate(SQLiteDatabase db) {

	// 用户信息表
	String userinfosql = "CREATE TABLE userinfo (_id  INTEGER PRIMARY KEY AUTOINCREMENT,usernumber varchar(20), username varchar(20),useradds varchar(20),usertype valchar(20),userstatus valchar(20)"
		+ " ,phone valchar(20),type varchar(20),ismultiplecopy varchar(10),copyerid varchar(10))";
	System.out.println(userinfosql + "用户信息");
	db.execSQL(userinfosql);

	String waterinfo = "create table waterinfo (_id  INTEGER PRIMARY KEY AUTOINCREMENT,waterlistid number,waternumber varchar(20),usernumber varchar(20),alarmlimit number,lowalarmlimit number,"
		+ "latitude number,longitude number,wateradds varchar(20),RFIDnumber varchar(20),lastmonthwater number,locationadds varchar(30),groupingname varchar(10),groupingsort number,groupwatersort number,"
		+ "thismonthwater number,metercaliber varchar(20),lastdate varchar(20),thisdate varchar(20),waterproperties varchar(20),copyerid varchar(10),copytime number,Navigationupload varchar(10)"
		+ ",bandingstatus varchar(10),iscopystatus varchar(10),isupload varchar(10),desc varchar(20),ismultiplecopy varchar(20),sid varchar(20),adds varchar(20),isnorfid varchar(10),Navigationdownload varchar(10))";

	System.out.println(waterinfo + "水表");
	db.execSQL(waterinfo);

	String usewaterinfosql = "create table usewaterinfo(_id  INTEGER PRIMARY KEY AUTOINCREMENT,waternumber varchar(20),month varchar(20),watercount varchar(20),"
		+ "  ismultiplecopy varchar(10),copyerid varchar(10) )";
	System.out.println(usewaterinfosql + "用户历史用水信息");
	db.execSQL(usewaterinfosql);

	String engineeringOrderSql = "create table engineeringOrder(_id  INTEGER PRIMARY KEY AUTOINCREMENT,waternumber varchar(20),ordertype varchar(20),"
		+ "desc varchar(100),imagepath varchar(20),videopath varchar(20),soundpath varchar(20),ordername varchar(20),status varchar(20),copyerid varchar(10) ,latitude number,longitude number)";
	System.out.println(engineeringOrderSql + "工程单");
	db.execSQL(engineeringOrderSql);

	String ordertypeSql = "create table type(_id  INTEGER PRIMARY KEY AUTOINCREMENT,number varchar(20),name varchar(20),status varchar(20),copyerid varchar(10))";
	System.out.println(ordertypeSql + "类型");
	db.execSQL(ordertypeSql);

	String UserTaskSql = "create table usertask(_id  INTEGER PRIMARY KEY AUTOINCREMENT,number varchar(20),name varchar(20),desc varchar(20),copyerid varchar(10))";
	System.out.println(UserTaskSql + "任务");
	db.execSQL(UserTaskSql);

	String ResourcesSql = "create table resources(_id  INTEGER PRIMARY KEY AUTOINCREMENT,otherid number,path varchar(40),type varchar(10),status varchar(10),copyerid varchar(10))";
	db.execSQL(ResourcesSql);
	System.out.println(ResourcesSql + "资源");

	String waterlistSql = "create table waterlist(_id INTEGER PRIMARY KEY AUTOINCREMENT,number varchar(20),name varchar(20),copyerid varchar(10))";
	db.execSQL(waterlistSql);
	System.out.println("表册" + waterlistSql);

	String otherequipmentSql = "create table otherequipment(_id INTEGER PRIMARY KEY AUTOINCREMENT, ep_number varchar(20), ep_type varchar(20),isnorfid varchar(10)"
		+ ",ep_typename varchar(20),ep_adds varchar(50),ep_locationadds varchar(30),latitude number,longitude number,ep_date varchar(20),isadd varchar(20),"
		+ "isupload varchar(20),ep_dess varchar(20),checkstatus varchar(20),ep_checkdesc varchar(50),ep_checktype varchar(50),ep_checkdate varchar(20),RFIDnumber varchar(20)"
		+ ",lastcheckdate varchar(20),lastcheckresult varchar(20),checkupload varchar(10),copyerid varchar(10))";
	db.execSQL(otherequipmentSql);

	System.out.println("其它设备" + otherequipmentSql);

	String imgresources = "create table imgresources(_id INTEGER PRIMARY KEY AUTOINCREMENT,imgpath varchar(20),imgwebname varchar(20),"
		+ "type varchar(20),othernumber varchar(20) ,status varchar(20),isdownload varchar(20),Sortnumber number,isimg varchar(20),copyerid varchar(10) )";
	db.execSQL(imgresources);
	System.out.println("资源导航" + imgresources);
    }

    /**
     * 当前版本号高于上次创建或升级的版本号，SQLiteOpenHelper会调用onUpdate()方法
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	db.execSQL("DROP TABLE IF EXISTS userinfo");
	db.execSQL("DROP TABLE IF EXISTS waterinfo");
	db.execSQL("DROP TABLE IF EXISTS usewaterinfo");
	db.execSQL("DROP TABLE IF EXISTS engineeringOrder");
	db.execSQL("DROP TABLE IF EXISTS type");
	db.execSQL("DROP TABLE IF EXISTS usertask");
	db.execSQL("DROP TABLE IF EXISTS resources");
	db.execSQL("DROP TABLE IF EXISTS waterlist");
	db.execSQL("DROP TABLE IF EXISTS otherequipment");
	db.execSQL("DROP TABLE IF EXISTS imgresources");
	onCreate(db);
    }

}
